<?php
// +----------------------------------------------------------------------
// | ThinkCMF [ WE CAN DO IT MORE SIMPLE ]
// +----------------------------------------------------------------------
// | Copyright (c) 2013-2017 http://www.thinkcmf.com All rights reserved.
// +----------------------------------------------------------------------
// | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
// +----------------------------------------------------------------------
// | Author: 老猫 <thinkcmf@126.com>
// +----------------------------------------------------------------------
namespace app\portal\controller;

use cmf\controller\AdminBaseController;
use think\Db;
use think\Loader;




class AdminStatisticsController extends AdminBaseController
{

    public function index()
    {
        $param = $this->request->param();
        $result = Db::name('Statistice')
            ->field('*,sg_time-zc_time as timestamp')
            ->order('timestamp', 'ASC')
            ->paginate(100);
        $data = $this->price($param);
        $count = $data['count'];
        $count_price = $data['count_price'];
        $this->assign('count', $count);
        $this->assign('count_price', $count_price);
        $this->assign('articles', $result->items());
        $this->assign('page', $result->render());

        return $this->fetch();
    }

    //数据导入


    public function excelAdd()
    {
        if ($this->request->isPost()) {
            $excel_name = $this->request->param('excel_name');
            $excel_url = $this->request->param('excel_url');
            if (empty($excel_url)) {
                $this->error('导入数据不可以为空!', url('portal/AdminStatistics/index'));
            }
            $count = Db::name('Statistice')->count();
            if ($count > 0) {
                Db::name('Statistice')->delete(true);
            }

            if (!empty($excel_name) && !empty($excel_url)) {
                Loader::import('PHPExcel.Classes.PHPExcel');
                Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
                Loader::import('PHPExcel.Classes.PHPExcel.Reader.Excel5');
                $filename = ROOT_PATH . 'public/upload/' . $excel_url;  //文件路径
                $extension = cmf_get_file_extension($excel_url);    //文件扩展名
                if ($extension == 'xlsx') {
                    $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
                    $objPHPExcel = $objReader->load($filename, $encode = 'utf-8');  //加载文件内容,编码utf-8
                } else if ($extension == 'xls') {
                    $objReader = \PHPExcel_IOFactory::createReader('Excel5');
                    $objPHPExcel = $objReader->load($filename, $encode = 'utf-8');  //加载文件内容,编码utf-8
                } else {
                    $this->error('请上传Excel格式的文件！', '');
                }
                $excel_array = $objPHPExcel->getsheet(0)->toArray();   //转换为数组格式
                array_shift($excel_array);  //删除第一个数组(标题);

                foreach ($excel_array as $k => $v) {
                    $data[$k]['zc_time'] = strtotime($v[0]);
                    $data[$k]['jh_time'] = strtotime($v[1]);
                    $data[$k]['sg_time'] = strtotime($v[2]);
                    $data[$k]['qr_time'] = strtotime($v[3]);
                    $data[$k]['phone'] = $v[4];
                    $data[$k]['status'] = $v[5];
                    $data[$k]['type'] = $v[6];
                    $data[$k]['fx_user'] = $v[7] ? $v[7] : "";
                    $data[$k]['ly_id'] = $v[8];
                    $data[$k]['wz_id'] = $v[9];
                    $data[$k]['tg_id'] = $v[10];
                    $data[$k]['tg_name'] = $v[11];
                    $data[$k]['dd_num'] = $v[12];
                    $data[$k]['hd_name'] = $v[13];
                    $data[$k]['is_valid'] = '合格';
                    $validate = Loader::validate('AdminStatistice');
                    if (!$validate->check($data[$k])) {
                        $this->error('第' . ($k + 2) . '行' . ' ' . $validate->getError(), '');
                    }
                }
                $datas = array_chunk($data, 4500);
                foreach ($datas as $v) {
                    if (Db::name('Statistice')->insertAll($v)) {

                    } else {
                        $this->error('导入数据失败!', url('portal/AdminStatistics/index'));
                    }
                }
                unset($excel_url);
                unlink($filename);
                $this->success('导入数据成功！', url('portal/AdminStatistics/index'));

            }
        }


        }


    //费用计算

    public function price($param)
    {
        set_time_limit(0);
        if (array_key_exists("date",$param)){
            $result = Db::name('Statistice')
                ->field('*,sg_time-zc_time as timestamp')
                ->order('timestamp', 'ASC')
                ->select();
            $date = explode(':', $param['date']);
            $j = cal_days_in_month(CAL_GREGORIAN, $date[0], 2018);
            $Y = "2018-".$date[0]."-01";
            $time = strtotime(date($Y));
            $day = array();

            for ($i = 1; $i < $j; $i++) {
                $end_time = $time + ($i + 1) * 86400;
                if ($i == 1) {
                    $start_time = $time;
                } else {
                    $start_time = $time + $i * 86400;
                }

                foreach ($result as $key => $row) {
                    if ($start_time < $row['sg_time'] && $row['sg_time'] < $end_time) {
                        $day[$i + 1][$key] = $result[$key];
                    }

                }

            }
            $A= array();
            $B = array();
            $C = array();
            foreach ($day as $key => $row) {
                if (count($row) <= 3) {
                    foreach ($row as $v) {
                        $A[] = $v;
                    }
                } elseif (count($row) <= 9) {
                    foreach (array_slice($row, 0, 3) as $v) {
                        $A[] = $v;
                    }
                    foreach (array_slice($row, 3) as $v) {
                        $B[] = $v;
                    }

                } elseif (count($row) >= 10) {
                    foreach (array_slice($row, 0, 3) as $v) {
                        $A[] = $v;
                    }
                    foreach (array_slice($row, 3, 6) as $v) {
                        $B[] = $v;
                    }
                    foreach (array_slice($row, 9) as $v) {
                        $C[] = $v;
                    }
                }

            }

            $A = count($A);
            $B = count($B);
            $C = count($C);
            $count_price = $A * $date[2] + $B * $date[3] + $C * $date[4];
            $count =ceil(($count_price - $date[1]) / 30) ;
            $list = $result->toArray();
            $data = array_slice($list, 0, $count);


//            foreach ($result as $v) {
//                if($v['is_valid'] == "不合格"){
//                    Db::name('Statistice')->where('is_valid', '不合格')->update(['is_valid' => '合格']);
//                }
//            }
            foreach ($data as $v) {
                Db::name('Statistice')->update(['is_valid' => '不合格', 'id' => $v['id']]);
            }
            $data = array("count" => $count, 'count_price' => $count_price);
        }else{
            $data = array("count" => '', 'count_price' =>'');
        }
        return $data;
    }


    //导出数据

    public function excelexport(){
        ini_set('memeory_limit','2048m');
        set_time_limit(0);
        Loader::import('excel.PHPExcel');//引入PHPExcel.php
        $PHPExcel = new \PHPExcel();//实例
        $PHPExcel->getActiveSheet()->setTitle("demo"); //给当前活动sheet设置名称
        $data = Db::name('Statistice')
            ->field('*,sg_time-zc_time as timestamp')
            ->order('timestamp', 'ASC')
            ->select();
        $data = $data->toArray();
        //$datas = array_chunk($data, 4500);

        $PHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '注册时间')
            ->setCellValue('B1', '激活时间')
            ->setCellValue('C1', '首购时间')
            ->setCellValue('D1', '确认收货时间')
            ->setCellValue('E1', '新人手机号')
            ->setCellValue('F1', '新人状态')
            ->setCellValue('G1', '订单类型')
            ->setCellValue('H1', '分享用户(unionId)')
            ->setCellValue('I1', '来源媒体ID')
            ->setCellValue('J1', '网站ID')
            ->setCellValue('K1', '推广位ID')
            ->setCellValue('L1', '推广位名称')
            ->setCellValue('M1', '订单编号')
            ->setCellValue('N1', '活动名称')
            ->setCellValue('O1', '是否合格');


            foreach($data as $k => $v){
                $num=$k+2;
                //定义标题
                   $PHPExcel->setActiveSheetIndex(0)
                    //Excel的第A列，id是你查出数组的键值，下面以此类推
                    ->setCellValue('A'.$num, date("Y-m-d H:i:s",$v['zc_time']))
                    ->setCellValue('B'.$num, date("Y-m-d H:i:s",$v['jh_time']))
                    ->setCellValue('C'.$num, date("Y-m-d H:i:s",$v['sg_time']))
                    ->setCellValue('D'.$num, date("Y-m-d H:i:s",$v['qr_time']))
                    ->setCellValue('E'.$num, $v['phone'])
                    ->setCellValue('F'.$num, $v['status'])
                    ->setCellValue('G'.$num, $v['type'])
                    ->setCellValue('H'.$num, $v['fx_user'])
                    ->setCellValue('I'.$num, $v['ly_id'])
                    ->setCellValue('J'.$num, $v['wz_id'])
                    ->setCellValue('K'.$num, $v['tg_id'])
                    ->setCellValue('L'.$num, $v['tg_name'])
                    ->setCellValue('M'.$num, $v['dd_num'])
                    ->setCellValue('N'.$num, $v['hd_name'])
                    ->setCellValue('O'.$num, $v['is_valid']);
            }

        $name ="数据明细";

        $PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel,"Excel2007");//创建生成的格式
        ob_end_clean();
        header('Content-Disposition: attachment;filename='.$name.'.xlsx');//下载下来的表格名
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $PHPWriter->save("php://output");//输出文件
        exit;
    }

   //导出详情
    public function export_all(){
       // $date =time();
        $model =Db::name('Statistice')->find();
        $time =  date("Y-m-d",$model['sg_time']);

        $date = explode('-', $time);
        $j = cal_days_in_month(CAL_GREGORIAN, $date[1], 2018);
        $Y = "2018-".$date[1]."-01";
        $time = strtotime(date($Y));
        $day = array();
        for ($i = 1; $i < $j; $i++) {
            $end_time = $time + ($i + 1) * 86400;
            if ($i == 1) {
                $start_time = $time;
            } else {
                $start_time = $time + $i * 86400;
            }
            $map['is_valid'] = array('eq','合格');
            $map['sg_time'] = array(array('gt',$start_time),array('lt',$end_time));
            $data[$i+1] = Db::name('Statistice')
                ->field('sg_time , tg_name, count(tg_name) as count,sg_time-zc_time as timestamp')
                ->where($map)
                ->group('tg_name')
                ->order('timestamp', 'ASC')
                ->select();

            $data[$i+1] = $data[$i+1]->toArray();
        }

        foreach($data as $v){
            $count = count($v);
            if($count>0){
                foreach($v as $vo){
                    $V[] =$vo;
                }
            }

        }
        Loader::import('excel.PHPExcel');//引入PHPExcel.php
        $PHPExcel = new \PHPExcel();//实例
        $PHPExcel->getActiveSheet()->setTitle("demo"); //给当前活动sheet设置名称

        foreach($V as $k => $v){
            $num=$k+2;
            //定义标题
            $PHPExcel->setActiveSheetIndex(0)
               ->setCellValue('A1', '日期')
                ->setCellValue('B1', '渠道名称')
                ->setCellValue('C1', '有效数量');

            $PHPExcel->setActiveSheetIndex(0)
                //Excel的第A列，id是你查出数组的键值，下面以此类推
                ->setCellValue('A'.$num, date("Y-m-d",$v['sg_time']))
                ->setCellValue('B'.$num, $v['tg_name'])
                ->setCellValue('C'.$num, $v['count']);
        }
        $name ="数据统计";

        $PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel,"Excel2007");//创建生成的格式
        ob_end_clean();
        header('Content-Disposition: attachment;filename='.$name.'.xlsx');//下载下来的表格名
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $PHPWriter->save("php://output");//输出文件
        exit;

    }

}